﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.IO;
using System.Security.Cryptography;
using Capstone_project.DatabaseAccessLayer;
using Capstone_project.BussinessObject;

namespace Capstone_project.DatabaseAccessLayer
{
    class Common
    {
        public static SqlConnection con = new SqlConnection(@"Data Source=HTC-PC\HTC;Initial Catalog=databaseDRM;Integrated Security=True");
        public static ArrayList username = new ArrayList();
        public static ArrayList password = new ArrayList();
        public static string permission;
        public static DataTable dt = new DataTable();
        public static DataSet ds = new DataSet();
        public static string selectedIDTASK;
        ConnectData conndata = new ConnectData();
        public static string checklogin(string userName, string Pass_word)
        {
            string pass = MaHoaMD5(Pass_word).Trim();
            con.Open();
            string quyen = "";
            SqlCommand cmd = new SqlCommand("CheckLogin", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = userName;
            cmd.Parameters.Add("@password", SqlDbType.NVarChar).Value = pass;
            SqlDataAdapter adap = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            adap.Fill(dt);
            con.Close();
            if (dt.Rows.Count > 0)
            {
                quyen = dt.Rows[0][0].ToString();
            }
            else quyen = "";
            return quyen;

        }
        public static Boolean CheckUserExist(string username)
        {
            if(con.State!=ConnectionState.Open)
            con.Open();
            SqlCommand cmd = new SqlCommand("SELECT * FROM USER_INFORMATION WHERE USER_NAME='" + username + "'", con);
            SqlDataAdapter adap = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            adap.Fill(dt);
            con.Close();
            if (dt.Rows.Count > 0)
                return false;
            return true;
        }
        public static Boolean CheckTaskAssignedtoUN(string idtask,string username)
        {
            if (con.State != ConnectionState.Open)
                con.Open();
            SqlCommand cmd = new SqlCommand("SELECT     USER_NAME, ID_TASK, START_DATE, END_DATE, COMPLETE, DESCRIPTION"
            +"  FROM         dbo.USER_TASK"
            +"  WHERE     (USER_NAME = '"+username+"') AND (ID_TASK = '"+idtask+"')", con);
            SqlDataAdapter adap = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            adap.Fill(dt);
            con.Close();
            if (dt.Rows.Count > 0)
                return false;
            return true;
        }
        public static Boolean CheckExistProject(string idproject)
        {
            if (con.State != ConnectionState.Open)
                con.Open();
            SqlCommand cmd = new SqlCommand("SELECT * from PROJECT where ID_PROJECT='"+idproject+"'", con);
            SqlDataAdapter adap = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            adap.Fill(dt);
            con.Close();
            if (dt.Rows.Count > 0)
                return false;
            return true;
        }
        public static int CountModuleofProject(string idproject)
        {
            int num = 0;
            string sqlcountmoduleofProject = "SELECT     COUNT(dbo.TASK_GROUP.ID_TASK_GROUP) AS Expr1"
                + " FROM         dbo.PROJECT INNER JOIN"
                + "      dbo.TASK_GROUP ON dbo.PROJECT.ID_PROJECT = dbo.TASK_GROUP.ID_PROJECT"
                + "    WHERE     (dbo.PROJECT.ID_PROJECT = '" + idproject + "')";
            if (con.State != ConnectionState.Open)
                con.Open();
            SqlCommand cmd = new SqlCommand(sqlcountmoduleofProject, con);
            SqlDataAdapter adap = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            adap.Fill(dt);
            con.Close();
            return num = int.Parse(dt.Rows[0][0].ToString());
        }
        public static bool CheckComplete(string idproject)
        {
            bool state;
            string sqlcountmoduleofProject = "SELECT     SUM(dbo.TASK.COMPLETE) / COUNT(dbo.TASK.ID_TASK) AS Expr1"
            + " FROM         dbo.TASK INNER JOIN"
            + "          dbo.TASK_GROUP ON dbo.TASK.ID_TASK_GROUP = dbo.TASK_GROUP.ID_TASK_GROUP AND "
            + "          dbo.TASK.ID_TASK_GROUP = dbo.TASK_GROUP.ID_TASK_GROUP"
            + " WHERE     (dbo.TASK_GROUP.ID_TASK_GROUP = '" + idproject + "')"
            + " GROUP BY dbo.TASK_GROUP.ID_TASK_GROUP";
            if (con.State != ConnectionState.Open)
                con.Open();
            SqlCommand cmd = new SqlCommand(sqlcountmoduleofProject, con);
            SqlDataAdapter adap = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            adap.Fill(dt);
            con.Close();
            if (dt.Rows.Count != 0)
            {
                if (dt.Rows[0][0].ToString() == "100")
                    return state = true;
                return state = false;
            }
            return state = false;
        }
        public static string MaHoaMD5(string text)
        {
            MD5CryptoServiceProvider _md5hasher = new MD5CryptoServiceProvider();
            byte[] bs = Encoding.UTF8.GetBytes(text);
            bs = _md5hasher.ComputeHash(bs);
            StringBuilder s = new StringBuilder();
            foreach (byte b in bs)
            {
                s.Append(b.ToString("x2"));
            }
            return s.ToString();
        }
        public static DataTable ReadDataTable(string query)
        {
            con.Open();
            SqlDataAdapter adap = new SqlDataAdapter(query, con);
            adap.Fill(dt);
            con.Close();
            return dt;
        }
        public static string NextID(string lastID, string prefixID)
        {
            int nextID;
            if (lastID == "")
            {
                return prefixID + "0001";  // Fixwidth với độ dài mặc định
            }
            else
            {
                nextID = int.Parse(lastID.Remove(0, prefixID.Length)) + 1;
                int lengthNumerID = lastID.Length - prefixID.Length;
                string zeroNumber = "";
                for (int i = 1; i <= lengthNumerID; i++)
                {
                    if (nextID < Math.Pow(10, i))
                    {
                        for (int j = 1; j <= lengthNumerID - i; i++)
                        {
                            zeroNumber += "0";
                        }
                        return prefixID + zeroNumber + nextID.ToString();
                    }
                }
            }
            return prefixID + nextID;

        }

        public object cmdExecuteScalar(string query)
        {
            con.Open();
            SqlCommand com = new SqlCommand(query, con);
            object ketqua = com.ExecuteScalar();
            con.Close();
            return ketqua;
        }
        public object cmdExecuteNonQuery(string query)
        {
            con.Open();
            SqlCommand com = new SqlCommand(query, con);
            object ketqua = com.ExecuteNonQuery();
            con.Close();
            return ketqua;
        }

    }
}
